<?php

function getLtisForCoach($teamId) {
	$sql = "select ld.id, reasonType, source, t.coachName, p1.id as playerOneId, p1.firstName as playerOneFirstName, p1.surname as playerOneSurname, p1.team as playerOneTeam, p1.playerType as playerOnePlayerType, ";
	$sql .= "p2.id as playerTwoId, p2.firstName as playerTwoFirstName, p2.surname as playerTwoSurname, p2.team as playerTwoTeam, p2.playerType as playerTwoPlayerType, ltiState from ltidelist ld inner join team t on ld.teamId = t.id";
	$sql .= " inner join player p1 on ld.playerOneId = p1.id inner join player p2 on ld.playerTwoId = p2.id where ld.teamId = $teamId and ltiState <> '" . LtiState::Closed . "';";
	
	return getLtiSpuds($sql);
}

function getReturnedLtisForCoach($teamId) {
	$sql = "select ld.id, reasonType, source, t.coachName, p1.id as playerOneId, p1.firstName as playerOneFirstName, p1.surname as playerOneSurname, p1.team as playerOneTeam, p1.playerType as playerOnePlayerType, p2.id as playerTwoId, ";
	$sql .= "p2.firstName as playerTwoFirstName, p2.surname as playerTwoSurname, p2.team as playerTwoTeam, p2.playerType as playerTwoPlayerType, ltiState from ltidelist ld inner join team t on ld.teamId = t.id";
	$sql .= " inner join player p1 on ld.playerOneId = p1.id inner join player p2 on ld.playerTwoId = p2.id where ld.teamId = $teamId and ltiState = '" . LtiState::Returned . "';";
	
	return getLtiSpuds($sql);
}

function getLtisSpudByLeague($leagueId) {
	$sql = "select ld.id, reasonType, source, t.coachName, p1.id as playerOneId, p1.firstName as playerOneFirstName, p1.surname as playerOneSurname, p1.team as playerOneTeam, p1.playerType as playerOnePlayerType, p2.id as playerTwoId,";
	$sql .= " p2.firstName as playerTwoFirstName, p2.surname as playerTwoSurname, p2.team as playerTwoTeam, p2.playerType as playerTwoPlayerType, ltiState from ltidelist ld inner join team t on ld.teamId = t.id";
	$sql .= " inner join player p1 on ld.playerOneId = p1.id inner join player p2 on ld.playerTwoId = p2.id where leagueId = $leagueId order by ld.id desc;";

	return getLtiSpuds($sql);
}

function getLtisByLeague($leagueId) {
	$sql = "select ld.id, reasonType, source, t.coachName, p1.id as playerOneId, p1.firstName as playerOneFirstName, p1.surname as playerOneSurname, p1.team as playerOneTeam, p1.playerType as playerOnePlayerType,";
	$sql .= " p2.id as playerTwoId, p2.firstName as playerTwoFirstName, p2.surname as playerTwoSurname, p2.team as playerTwoTeam, p2.playerType as playerTwoPlayerType, ltiState from ltidelist ld";
	$sql .= " inner join team t on ld.teamId = t.id inner join player p1 on ld.playerOneId = p1.id inner join player p2 on ld.playerTwoId = p2.id where reasonType = '" . TradeType::Lti . "' and leagueId = $leagueId order by ld.id desc;";

	return getLtiSpuds($sql);
}

function getCountLtiSpudsByLeague($leagueId) {
	$sql = "select count(*) as count from ltidelist where teamId in (select id from team where leagueId = $leagueId) order by id desc";
	$result = mysql_query($sql);
	$row = mysql_fetch_array($result);
	$count = $row['count'];

	
	return $count;
}

function checkReturnedLtis($ltiList) {
	$sql = "update ltidelist set ltiState = '" . LtiState::Returned . "' where id in ($ltiList);";
	echo $sql;
	mysql_query($sql);
}

function removeLti($ltiId, $playerToReturn) {
	$sql = "select * from ltidelist where id = $ltiId";
	$result = mysql_query($sql);
	$row = mysql_fetch_array($result);
	$playerOneId = $row['playerOneId'];
	$playerTwoId = $row['playerTwoId'];
	$teamId = $row['teamId'];
	$playerToKeep = $playerOneId;
	
	if ($playerToReturn == $playerOneId) {
		$playerToKeep = $playerTwoId;
	}
	
	$sql = "update teamplayer set isLti = 0, isLtiReplacement = 0, traded = 1 where teamId = $teamId and playerId = $playerToReturn;";
	mysql_query($sql);
	$sql = "update teamplayer set isLti = 0, isLtiReplacement = 0 where teamId = $teamId and playerId = $playerToKeep;";
	mysql_query($sql);
	
	$sql = "update ltidelist set ltistate = 'CLOSED' where id = $ltiId;";
	mysql_query($sql);
}

function getLtiSpuds($sql) {
	
	$tradeResult = mysql_query($sql);
	
	$array = array();
	$count = 1;
	while($row = mysql_fetch_array($tradeResult)) {
		$ltiSpud = new LtiSpud();
		$ltiSpud->id = $row['id'];
        $ltiSpud->coachName = $row['coachName']; 
        $player = new Player();
        $player->id = $row['playerOneId'];        
        $player->playerFirstName = $row['playerOneFirstName'];
        $player->playerSurname = $row['playerOneSurname'];
        $player->team = $row['playerOneTeam'];
        $player->playerType = $row['playerOnePlayerType'];
        $ltiSpud->playerOne = $player;
        $player = new Player();
        $player->id = $row['playerTwoId'];
        $player->playerFirstName = $row['playerTwoFirstName'];
        $player->playerSurname = $row['playerTwoSurname'];
        $player->team = $row['playerTwoTeam'];
        $player->playerType = $row['playerTwoPlayerType'];
        $ltiSpud->playerTwo = $player;
        $ltiSpud->reasonType = $row['reasonType'];
        $ltiSpud->source = $row['source'];
        $ltiSpud->ltiState = $row['ltiState'];

        $array[] = $ltiSpud;
		$count++;		
	}
	
	
	return $array;
}

function getLtiSpud($leagueId) {
	$sql = "select reasonType, source, t.coachName, p1.firstName as playerOneFirstName, p1.surname as playerOneSurname, p1.team as playerOneTeam, p1.playerType as playerOnePlayerType, p2.firstName as playerTwoFirstName,";
	$sql .= " p2.surname as playerTwoSurname, p2.team as playerTwoTeam, p2.playerType as playerTwoPlayerType from ltidelist ld ";
	$sql .= "inner join team t on ld.teamId = t.id inner join player p1 on ld.playerOneId = p1.id inner join player p2 on ld.playerTwoId = p2.id where state = '" . LtiSubmittedStatus::Submitted . "' and leagueId = $leagueId;";
	
	$tradeResult = mysql_query($sql);
	
	$array = array();
	while($row = mysql_fetch_array($tradeResult)) {
		$ltiSpud = new LtiSpud();
		$ltiSpud->coachName = $row['coachName']; 
		$player = new Player();
		$player->playerFirstName = $row['playerOneFirstName'];
		$player->playerSurname = $row['playerOneSurname'];
		$player->team = $row['playerOneTeam'];
		$player->playerType = $row['playerOnePlayerType'];
		$ltiSpud->playerOne = $player;
		$player = new Player();
		$player->playerFirstName = $row['playerTwoFirstName'];
		$player->playerSurname = $row['playerTwoSurname'];
		$player->team = $row['playerTwoTeam'];
		$player->playerType = $row['playerTwoPlayerType'];
		$ltiSpud->playerTwo = $player;
		$ltiSpud->reasonType = $row['reasonType'];
		$ltiSpud->source = $row['source'];
		
		$array[] = $ltiSpud;
	}
	
	
	return $array;
}

function createLTISpud($playerOneId, $playerTwoId, $reasonType, $source, $teamId) {
	if ($reasonType != null && $playerOneId != null && $playerTwoId != null) {
		$sql = "INSERT INTO ltidelist ";
	   	if ($source != null) {
	   		$sql .= "(teamId, playerOneId, playerTwoId, reasonType, source, state)";
	   	}
	   	else {
			$sql .= "(teamId, playerOneId, playerTwoId, reasonType, state)";
	   	}
	   	$sql .= " VALUES (" . $teamId . "," . $playerOneId . "," . $playerTwoId . ",'" . $reasonType . "'";
	   
	   	if ($source != null) {
			$sql .= ", '" . $source . "', '" . LtiSubmittedStatus::Submitted . "');";
	   	}
	   	else {
			$sql .= ", '" . LtiSubmittedStatus::Submitted . "');";
	   	}
	   	mysql_query($sql);
	
	   	if ($reasonType == TradeType::Lti) {
			$teamPlayerSQL = "INSERT into teamplayer (playerId, teamId, playerPosition, isLtiReplacement) values (" . $playerTwoId . "," . $teamId . ", 'RES', 1);";
			mysql_query($teamPlayerSQL);
	
			$updateTeamPlayerSQL = "UPDATE teamplayer set islti = true where playerId = $playerOneId and teamId = $teamId;";
	   	}
	   	else {
			$teamPlayerSQL = "INSERT into teamplayer (playerId, teamId, playerPosition) values (" . $playerTwoId . "," . $teamId . ", 'RES');";
			mysql_query($teamPlayerSQL);
	
			$updateTeamPlayerSQL = "UPDATE teamplayer set traded = true where playerId = $playerOneId and teamId = $teamId;";
	   	}
	
	   	mysql_query($updateTeamPlayerSQL);
	}
}

function updateLtiDelistToInformed($leagueId) {	
	$sql = "UPDATE ltidelist set state = '" . LtiSubmittedStatus::Submitted_Informed . "' where state = '" . LtiSubmittedStatus::Submitted . "' and teamId in (select id from team where leagueId = $leagueId);";
	mysql_query($sql);
}

?>
